<?php require 'db.php'; ?>
<!DOCTYPE html>
<html lang="en">
<head>
    <title>Admin Panel - Projects</title>
    <link href="css/bootstrap-responsive.css" rel="stylesheet" type="text/css" />
    <link href="css/bootstrap.css" rel="stylesheet" type="text/css" />
    <link href="css/style.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div class="container">
    <header>
        <h3 class="muted">Admin Panel</h3>
        <div class="navbar">
            <div class="navbar-inner">
                <div class="container">
                    <ul class="nav">
                        <li>
                            <a href="index.php">Home</a>
                        </li>
                        <li class="dropdown active">
                            <a href="#" class="dropdown-toggle" data-toggle="dropdown">Projects <b class="caret"></b></a>
                            <ul class="dropdown-menu">
                                <li><a href="projects.php">View</a></li>
                                <li><a href="project.php">Add Project</a></li>
                            </ul>
                        </li>
                        <li class="dropdown">
                            <a href="#" class="dropdown-toggle" data-toggle="dropdown">Architects <b class="caret"></b></a>
                            <ul class="dropdown-menu">
                                <li><a href="architects.php">View</a></li>
                                <li><a href="architect.php">Add Architect</a></li>
                            </ul>
                        </li>
                        <li>
                            <a href="#">Settings</a>
                        </li>
                    </ul>
                </div>
            </div>
        </div>
    </header>
    <section>
        <ul class="breadcrumb">
            <li><a href="index.php">Home</a> <span class="divider">/</span></li>
            <li class="active">Projects</li>
        </ul>

            <div class="row-fluid">
                <div class="span8">
                    <form action="" method="GET" class="filter">
                        <?php
                        $architects = mysqli_query($con, "SELECT `architects`.`id`, `architects`.`name` FROM `projects`
                                                                JOIN `architects` WHERE `projects`.`architect_id` = `architects`.`id`
                                                                GROUP BY `projects`.`architect_id`");
                        ?>
                        <select name="architect">
                            <option value="">All Architects</option>
                        <?php while($architect = mysqli_fetch_assoc($architects)) { ?>
                            <option <?php if($_GET['architect'] == $architect['id']){ ?>selected="true"<?php } ?> value="<?php echo $architect['id']; ?>"><?php echo $architect['name']; ?></option>
                        <?php } ?>
                        </select>
                        <?php
                        $start_year = mysqli_query($con, "SELECT YEAR(`projects`.`start_date`) AS  `year`
                                                    FROM `projects`
                                                    GROUP BY `year`
                                                    ORDER BY `year` ASC
                                                    LIMIT 1");

                        $finish_year = mysqli_query($con, "SELECT YEAR(`projects`.`finish_date`) AS  `year`
                                                    FROM `projects`
                                                    GROUP BY `year`
                                                    ORDER BY `year` DESC
                                                    LIMIT 1");

                        $start_year = mysqli_fetch_assoc($start_year);
                        $start_year = $start_year['year'];

                        $finish_year = mysqli_fetch_assoc($finish_year);
                        $finish_year = $finish_year['year'];
                        ?>
                        <select name="year">
                            <option value="">All Year</option>
                            <?php while($start_year <= $finish_year){ ?>
                            <option <?php if($start_year == $_GET['year']){?>selected="true"<?php } ?> ><?php echo $start_year++; ?></option>
                            <?php } ?>
                        </select>
                        <input type="submit" class="btn" value="Sort" />
                    </form>
                </div>

                <form action="project.php" data-action="project.php" method="get">
                    <input name="action" value="add"  type="hidden" />
                    <div class="span4 text-right">
                        <button type="submit" id="add" class="btn btn-primary">Add</button>
                        <button type="submit" id="edit" disabled class="btn btn-primary">Edit</button>
                        <button type="submit" id="delete" disabled class="btn btn-danger">Delete</button>
                    </div>
                </form>
            </div>
            <hr />
            <table class="table table-hover">
            <thead>
            <tr>
                <th>#</th>
                <th>Project Name</th>
                <th>Start Date</th>
                <th>Finish Date</th>
                <th>Architect</th>
                <th>Cost</th>
            </tr>
            </thead>
            <tbody>
            <?php
            $q = "SELECT `projects`.`id`, `projects`.`name`, `projects`.`start_date`,
                                        `projects`.`finish_date`, `projects`.`cost`, `projects`.`architect_id`,
                                        `architects`.`name` as `architect` FROM `projects`
                                        left JOIN `architects`
                                        AS `architects`
                                        ON `projects`.`architect_id` = `architects`.`id`";
            if($_GET['architect'] && $_GET['year']){
                $q .= ' WHERE "'.$_GET["year"].'" BETWEEN YEAR(`projects`.`start_date`) AND YEAR(`projects`.`finish_date`)
                        AND `projects`.`architect_id` = "'.$_GET["architect"].'"';
            }else{
                if($_GET['architect']){
                    $q .= ' WHERE `projects`.`architect_id` = "'.$_GET["architect"].'"';
                }
                if($_GET['year']){
                    $q .= ' WHERE "'.$_GET["year"].'" BETWEEN YEAR(`projects`.`start_date`) AND YEAR(`projects`.`finish_date`)';
                }
            }

            $result = mysqli_query($con, $q);

            $count = 0;
            while($row = mysqli_fetch_assoc($result))
            {
                $count++;
                ?>
                <tr>
                    <td>
                        <label class="checkbox">
                            <input name="id[]" value="<?php echo $row['id']; ?>" type="checkbox" />
                            <?php echo $count; ?>
                        </label>
                    </td>
                    <td><?php echo $row['name']; ?></td>
                    <td><?php echo $row['start_date']; ?></td>
                    <td><?php echo $row['finish_date']; ?></td>
                    <td><a href="architects.php?id=<?php echo $row['architect_id']; ?>"><?php echo $row['architect']; ?></a></td>
                    <td>$<?php echo $row['cost']; ?></td>
                </tr>
            <?php }

            mysqli_close($con);
            ?>
            </tbody>
        </table>
        <!--
        <hr />
        <div class="pagination">
            <ul>
                <li><a href="#">Prev</a></li>
                <li><a href="#">1</a></li>
                <li><a href="#">2</a></li>
                <li><a href="#">3</a></li>
                <li><a href="#">4</a></li>
                <li><a href="#">5</a></li>
                <li><a href="#">Next</a></li>
            </ul>
        </div>
        -->
    </section>
    <footer>
        <hr />
        <p>&copy; Company 2013</p>
    </footer>
</div>
<script src="http://code.jquery.com/jquery.js"></script>
<script src="js/bootstrap.js"></script>
<script src="js/default.js"></script>
</body>
</html>